﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QuanLyKhachHang.Forms
{
    #region using
    using QuanLyKhachHang.BUS;
    using QuanLyKhachHang.DTO;
    using QuanLyKhachHang.Utilities;
    using System.IO;
    using NPOI.HSSF.UserModel;
    using NPOI.HPSF;
    using NPOI.SS.UserModel;
    using NPOI.HSSF.UserModel.Contrib;
    using NPOI.HSSF.Util;
    using NPOI.SS.Util;
    #endregion

    public partial class frmBCKhachHangLon : Form
    {
        private delegate void TimeTask();
        private frmWaiting waiting;
        private DataSet objDonViDS;
        private DataSet objCSUDDS;
        private DataTable dataTable = new DataTable();
        private string maDonVi;
        private int thang;
        private int nam;
        private HSSFWorkbook hssfworkbook;

        public frmBCKhachHangLon()
        {
            InitializeComponent();
            InitGridViewHienThi();
        }

        private void WaitLoad()
        {
            waiting = new frmWaiting("Đang lấy dữ liệu");
            waiting.ShowDialog();
        }

        public void InitGridViewHienThi()
        {
            string str = "";
            long i = 0;
            DataColumn col1 = new DataColumn("STT", str.GetType());
            DataColumn col2 = new DataColumn("TENKH", str.GetType());
            DataColumn col3 = new DataColumn("DIACHI", str.GetType());

            DataColumn col4 = new DataColumn("BPTN", str.GetType());
            DataColumn col5 = new DataColumn("BKTN", str.GetType());
            DataColumn col6 = new DataColumn("EMSTN", str.GetType());
            DataColumn col7 = new DataColumn("EMSNN", str.GetType());
            DataColumn col8 = new DataColumn("VE", str.GetType());
            DataColumn col9 = new DataColumn("BCUT", str.GetType());
            DataColumn col10 = new DataColumn("TG", str.GetType());
            DataColumn col11 = new DataColumn("TM", str.GetType());
            DataColumn col12 = new DataColumn("TBT", str.GetType());

            DataColumn col13 = new DataColumn("MAKH", str.GetType());
            DataColumn col14 = new DataColumn("TONG", str.GetType());
            DataColumn col15 = new DataColumn("TONGDOANHTHUTRICHTHUONG", str.GetType());
            DataColumn col16 = new DataColumn("TONGDOANHTHUCHIETKHAU", str.GetType());
            DataColumn col17 = new DataColumn("SOTIENTRICHTHUONG", str.GetType());
            DataColumn col18 = new DataColumn("SOTIENCHIETKHAU", str.GetType());

            dataTable.Columns.Add(col1);
            dataTable.Columns.Add(col2);
            dataTable.Columns.Add(col3);
            dataTable.Columns.Add(col4);
            dataTable.Columns.Add(col5);
            dataTable.Columns.Add(col6);
            dataTable.Columns.Add(col7);
            dataTable.Columns.Add(col8);
            dataTable.Columns.Add(col9);
            dataTable.Columns.Add(col10);
            dataTable.Columns.Add(col11);
            dataTable.Columns.Add(col12);
            dataTable.Columns.Add(col13);
            dataTable.Columns.Add(col14);
            dataTable.Columns.Add(col15);
            dataTable.Columns.Add(col16);
            dataTable.Columns.Add(col17);
            dataTable.Columns.Add(col18);
        }


        public void LoadDonVi()
        {
            var objBUS = new DonViBUS(GlobalVariables.g_strConnectionString);

            try
            {
                objDonViDS = objBUS.SelectAll();

                for (int i = 0; i < objDonViDS.Tables["DonVi"].Rows.Count; i++)
                {
                    DataRow row = objDonViDS.Tables["DonVi"].Rows[i];
                    cbxDonVi.Items.Add(new DonViDTO { MADONVI = row["MADONVI"].ToString(), TENDONVI = row["TENDONVI"].ToString() });
                }

                if (cbxDonVi.Items.Count > 0)
                    cbxDonVi.SelectedIndex = 0;
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public void LoadCSUD()
        {
            var objBUS = new CS_UUDAIBUS(GlobalVariables.g_strConnectionString);

            try
            {
                objCSUDDS = objBUS.SelectAll();
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public bool ValidateInput()
        {
            if (cbxDonVi.Text.Equals(""))
            {
                MessageBox.Show("Bạn chưa chọn đơn vị", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }

            if (txtThang.Text.Equals(""))
            {
                MessageBox.Show("Bạn chưa nhập tháng", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }

            if (txtNam.Text.Equals(""))
            {
                MessageBox.Show("Bạn chưa nhập năm", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }

            if (!Utils.IsNumeric(txtThang.Text))
            {
                MessageBox.Show("Tháng phải là số hợp lệ", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }
            else
            {
                if (int.Parse(txtThang.Text) < 1 || int.Parse(txtThang.Text) > 12)
                {
                    MessageBox.Show("Tháng phải từ 1-12 ", "Bao Loi",
                        MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return false;
                }
            }

            if (!Utils.IsNumeric(txtNam.Text))
            {
                MessageBox.Show("Năm phải là số hợp lệ", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }
            else
            {
                if (int.Parse(txtNam.Text) <= 0)
                {
                    MessageBox.Show("Năm phải từ > 0 ", "Bao Loi",
                        MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return false;
                }
            }

            return true;
        }

        public bool HaveDataInRow(DataTable _tbl, int index)
        {
            try
            {
                DataRow row = _tbl.Rows[index];
                return true;
            }
            catch
            {
                return false;
            }
        }

        private void btnXem_Click(object sender, EventArgs e)
        {
            if (ValidateInput())
            {
                thang = int.Parse(txtThang.Text);
                nam = int.Parse(txtNam.Text);
                maDonVi = ((DonViDTO)cbxDonVi.SelectedItem).MADONVI;
                dataTable.Clear();
                dtgvDanhSachHienThi.DataSource = null;
                dtgvDanhSachHienThi.Columns.Clear();

                var objBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
                var objDS = objBUS.SelectByMADONVI(maDonVi, int.Parse(txtThang.Text), int.Parse(txtNam.Text));

                if (objDS.Tables[0].Rows.Count > 0)
                {
                    int currentIndex = 0;
                    for (int i = 0; i < objDS.Tables[0].Rows.Count; i++)
                    {
                        bool contains = false;

                        for (int j = 0; j <= currentIndex; j++)
                        {
                            if (HaveDataInRow(dataTable, j))
                            {
                                if (objDS.Tables[0].Rows[i]["MAKH"].ToString() == (dataTable.Rows[j]["MAKH"].ToString()))
                                {
                                    contains = true;
                                    break;
                                }
                            }
                        }

                        if (!contains)
                        {
                            DataRow row2;
                            dataTable.Rows.Add();
                            row2 = dataTable.Rows[currentIndex];

                            row2["STT"] = (currentIndex + 1).ToString();
                            row2["MAKH"] = objDS.Tables[0].Rows[i]["MAKH"].ToString();
                            row2["TENKH"] = objDS.Tables[0].Rows[i]["TENKH"].ToString();
                            row2["DIACHI"] = objDS.Tables[0].Rows[i]["DIACHI"].ToString();

                            currentIndex++;
                        }
                    }

                    //set data for TrichThuongChietKhau Column
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        DataRow row = dataTable.Rows[i];
                        var objTTCKBUS = new TrichThuong_ChietKhauBUS(GlobalVariables.g_strConnectionString);
                        var objTTCKDS = objTTCKBUS.SelectByID(int.Parse(row["MAKH"].ToString()), thang, nam);
                        dataTable.Rows[i]["TONGDOANHTHUTRICHTHUONG"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["TONGTRICHTHUONG"].ToString());
                        dataTable.Rows[i]["TONGDOANHTHUCHIETKHAU"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["TONGCHIETKHAU"].ToString());
                        dataTable.Rows[i]["SOTIENTRICHTHUONG"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["CHITRICHTHUONG"].ToString());
                        dataTable.Rows[i]["SOTIENCHIETKHAU"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["CHICHIETKHAU"].ToString());
                    }

                    //set data for TONGDOANHTHU Column
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        int count = 0;
                        long tongtien = 0;
                        int indexCol = 0;
                        for (int j = 0; j < objDS.Tables[0].Rows.Count; j++)
                        {
                            if (dataTable.Rows[i]["MAKH"].ToString() == objDS.Tables[0].Rows[j]["MAKH"].ToString())
                            {
                                dataTable.Rows[i][indexCol + 3] = objDS.Tables[0].Rows[j]["SOTIEN"];
                                tongtien += long.Parse(objDS.Tables[0].Rows[j]["SOTIEN"].ToString());
                                count++;
                                indexCol++;
                                if (count == 9)
                                {
                                    dataTable.Rows[i]["TONG"] = tongtien.ToString();
                                    count = 0;
                                    break;
                                }
                            }
                        }

                    }

                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        dataTable.Rows[i]["TONG"] = string.Format("{0:#,##0;Nothing}", long.Parse(dataTable.Rows[i]["TONG"].ToString()));
                        dataTable.Rows[i]["TONGDOANHTHUTRICHTHUONG"] = string.Format("{0:#,##0;Nothing}", long.Parse(dataTable.Rows[i]["TONGDOANHTHUTRICHTHUONG"].ToString()));
                        dataTable.Rows[i]["TONGDOANHTHUCHIETKHAU"] = string.Format("{0:#,##0;Nothing}", long.Parse(dataTable.Rows[i]["TONGDOANHTHUCHIETKHAU"].ToString()));
                        dataTable.Rows[i]["SOTIENTRICHTHUONG"] = string.Format("{0:#,##0;Nothing}", long.Parse(dataTable.Rows[i]["SOTIENTRICHTHUONG"].ToString()));
                        dataTable.Rows[i]["SOTIENCHIETKHAU"] = string.Format("{0:#,##0;Nothing}", long.Parse(dataTable.Rows[i]["SOTIENCHIETKHAU"].ToString()));
                    }

                    DataView objTimeSheetDV = new System.Data.DataView(dataTable);
                    objTimeSheetDV.AllowNew = false;
                    dtgvDanhSachHienThi.AutoGenerateColumns = false;
                    dtgvDanhSachHienThi.DataSource = objTimeSheetDV;

                    DataGridViewTextBoxColumn objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "STT",
                        DataPropertyName = "STT",
                        Name = "STT",
                        Width = 30
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "MAKH",
                        Name = "MAKH",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tên KH",
                        DataPropertyName = "TENKH",
                        Name = "TENKH",
                        Width = 150
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Địa chỉ",
                        DataPropertyName = "DIACHI",
                        Name = "DIACHI",
                        Width = 150
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BPTN",
                        Name = "BPTN",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BKTN",
                        Name = "BKTN",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "EMSTN",
                        Name = "EMSTN",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "EMSNN",
                        Name = "EMSNN",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "VE",
                        Name = "VE",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BCUT",
                        Name = "BCUT",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TG",
                        Name = "TG",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TM",
                        Name = "TM",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TBT",
                        Name = "TBT",
                        Visible = false
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tổng",
                        DataPropertyName = "TONG",
                        Name = "TONG",
                        Width = 75
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tổng doanh thu TT",
                        DataPropertyName = "TONGDOANHTHUTRICHTHUONG",
                        Name = "TONGDOANHTHUTRICHTHUONG",
                        Width = 75
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tổng doanh thu CK",
                        DataPropertyName = "TONGDOANHTHUCHIETKHAU",
                        Name = "TONGDOANHTHUCHIETKHAU",
                        Width = 75
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Số tiền TT",
                        DataPropertyName = "SOTIENTRICHTHUONG",
                        Name = "SOTIENTRICHTHUONG",
                        Width = 75
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Số tiền CK",
                        DataPropertyName = "SOTIENCHIETKHAU",
                        Name = "SOTIENCHIETKHAU",
                        Width = 75
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    dtgvDanhSachHienThi.ClearSelection();
                    btnLapBaoCao.Enabled = true;
                }
            }
        }

        private void dtgvDanhSachHienThi_Click(object sender, EventArgs e)
        {
            try
            {
                txtTenKH.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["TENKH"].Value.ToString();
                txtDiaChi.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["DIACHI"].Value.ToString();
                txtBPTN.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["BPTN"].Value.ToString();
                txtBKTN.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["BKTN"].Value.ToString();
                txtVExpress.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["VE"].Value.ToString();
                txtEMSTN.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["EMSTN"].Value.ToString();
                txtEMSNN.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["EMSNN"].Value.ToString();
                txtBCUT.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["BCUT"].Value.ToString();
                txtTemGiay.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["TG"].Value.ToString();
                txtTemMay.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["TM"].Value.ToString();
                txtTheBuuThiep.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["TBT"].Value.ToString();
                txtTongDoanhThu.Text = dtgvDanhSachHienThi.SelectedRows[0].Cells["TONGDOANHTHUTRICHTHUONG"].Value.ToString();
            }
            catch
            {
            }
        }

        private void frmBCKhachHangLon_Load(object sender, EventArgs e)
        {
            LoadDonVi();
        }

        public void LapBaoCaoDuLieu_CacDonViConLai(Sheet sheet1)
        {
            dataTable = new DataTable();
            InitGridViewHienThi();
            thang = int.Parse(txtThang.Text);
            nam = int.Parse(txtNam.Text);
            var objBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
            var objDS = objBUS.SelectByMADONVI(maDonVi, thang, nam);

            if (objDS.Tables[0].Rows.Count > 0)
            {
                int currentIndex = 0;
                for (int i = 0; i < objDS.Tables[0].Rows.Count; i++)
                {
                    bool contains = false;

                    for (int j = 0; j <= currentIndex; j++)
                    {
                        if (HaveDataInRow(dataTable, j))
                        {
                            if (objDS.Tables[0].Rows[i]["MAKH"].ToString() == (dataTable.Rows[j]["MAKH"].ToString()))
                            {
                                contains = true;
                                break;
                            }
                        }
                    }

                    if (!contains)
                    {
                        DataRow row2;
                        dataTable.Rows.Add();
                        row2 = dataTable.Rows[currentIndex];

                        row2["STT"] = (currentIndex + 1).ToString();
                        row2["MAKH"] = objDS.Tables[0].Rows[i]["MAKH"].ToString();
                        row2["TENKH"] = objDS.Tables[0].Rows[i]["TENKH"].ToString();
                        row2["DIACHI"] = objDS.Tables[0].Rows[i]["DIACHI"].ToString();

                        currentIndex++;
                    }
                }

                //set data for TrichThuongChietKhau Column
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    DataRow row = dataTable.Rows[i];
                    var objTTCKBUS = new TrichThuong_ChietKhauBUS(GlobalVariables.g_strConnectionString);
                    var objTTCKDS = objTTCKBUS.SelectByID(int.Parse(row["MAKH"].ToString()), thang, nam);
                    dataTable.Rows[i]["TONGDOANHTHUTRICHTHUONG"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["TONGTRICHTHUONG"].ToString());
                    dataTable.Rows[i]["TONGDOANHTHUCHIETKHAU"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["TONGCHIETKHAU"].ToString());
                    dataTable.Rows[i]["SOTIENTRICHTHUONG"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["CHITRICHTHUONG"].ToString());
                    dataTable.Rows[i]["SOTIENCHIETKHAU"] = long.Parse(objTTCKDS.Tables[0].Rows[0]["CHICHIETKHAU"].ToString());
                }

                //set data for TONGDOANHTHU Column
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    int count = 0;
                    long tongtien = 0;
                    int indexCol = 0;
                    for (int j = 0; j < objDS.Tables[0].Rows.Count; j++)
                    {
                        if (dataTable.Rows[i]["MAKH"].ToString() == objDS.Tables[0].Rows[j]["MAKH"].ToString())
                        {
                            dataTable.Rows[i][indexCol + 3] = objDS.Tables[0].Rows[j]["SOTIEN"];
                            tongtien += long.Parse(objDS.Tables[0].Rows[j]["SOTIEN"].ToString());
                            count++;
                            indexCol++;
                            if (count == 9)
                            {
                                dataTable.Rows[i]["TONG"] = tongtien.ToString();
                                count = 0;
                                break;
                            }
                        }
                    }

                }
            }

            int startRow = 9;
            DataFormat format = hssfworkbook.CreateDataFormat();
            Font font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;
            numberStyle.SetFont(font);

            CellStyle sttStyle = hssfworkbook.CreateCellStyle();
            sttStyle.DataFormat = format.GetFormat("#,#0");
            sttStyle.BorderBottom = CellBorderType.THIN;
            sttStyle.BorderLeft = CellBorderType.THIN;
            sttStyle.BorderRight = CellBorderType.THIN;
            sttStyle.BorderTop = CellBorderType.THIN;
            sttStyle.Alignment = HorizontalAlignment.CENTER;
            sttStyle.VerticalAlignment = VerticalAlignment.CENTER;
            sttStyle.SetFont(font);

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;
            textStyle.SetFont(font);

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                Row createRow = sheet1.CreateRow(startRow);
                createRow.Height = 400;

                Cell cell0 = createRow.CreateCell(0);
                cell0.SetCellValue(double.Parse(dataTable.Rows[i]["STT"].ToString()));
                cell0.CellStyle = sttStyle;

                Cell cell1 = createRow.CreateCell(1);
                cell1.SetCellValue(dataTable.Rows[i]["TENKH"].ToString());
                cell1.CellStyle = textStyle;

                Cell cell2 = createRow.CreateCell(2);
                cell2.SetCellValue(dataTable.Rows[i]["DIACHI"].ToString());
                cell2.CellStyle = textStyle;

                Cell cell3 = createRow.CreateCell(3);
                cell3.SetCellValue(double.Parse(dataTable.Rows[i]["BPTN"].ToString()));
                cell3.CellStyle = numberStyle;

                Cell cell4 = createRow.CreateCell(4);
                cell4.SetCellValue(double.Parse(dataTable.Rows[i]["BKTN"].ToString()));
                cell4.CellStyle = numberStyle;

                Cell cell5 = createRow.CreateCell(5);
                cell5.SetCellValue(double.Parse(dataTable.Rows[i]["EMSTN"].ToString()));
                cell5.CellStyle = numberStyle;

                Cell cell6 = createRow.CreateCell(6);
                cell6.SetCellValue(double.Parse(dataTable.Rows[i]["EMSNN"].ToString()));
                cell6.CellStyle = numberStyle;

                Cell cell7 = createRow.CreateCell(7);
                cell7.SetCellValue(double.Parse(dataTable.Rows[i]["VE"].ToString()));
                cell7.CellStyle = numberStyle;

                Cell cell8 = createRow.CreateCell(8);
                cell8.SetCellValue(double.Parse(dataTable.Rows[i]["BCUT"].ToString()));
                cell8.CellStyle = numberStyle;

                Cell cell9 = createRow.CreateCell(9);
                cell9.SetCellValue(double.Parse(dataTable.Rows[i]["TBT"].ToString()));
                cell9.CellStyle = numberStyle;

                Cell cell10 = createRow.CreateCell(10);
                cell10.SetCellValue(double.Parse(dataTable.Rows[i]["TONG"].ToString()));
                cell10.CellStyle = numberStyle;

                Cell cell11 = createRow.CreateCell(11);
                cell11.SetCellValue(double.Parse(dataTable.Rows[i]["TONGDOANHTHUTRICHTHUONG"].ToString()));
                cell11.CellStyle = numberStyle;

                Cell cell12 = createRow.CreateCell(12);
                cell12.SetCellValue(double.Parse(dataTable.Rows[i]["TONGDOANHTHUCHIETKHAU"].ToString()));
                cell12.CellStyle = numberStyle;

                Cell cell13 = createRow.CreateCell(13);
                cell13.SetCellValue(double.Parse(dataTable.Rows[i]["SOTIENTRICHTHUONG"].ToString()));
                cell13.CellStyle = numberStyle;

                Cell cell14 = createRow.CreateCell(14);
                cell14.SetCellValue(double.Parse(dataTable.Rows[i]["SOTIENCHIETKHAU"].ToString()));
                cell14.CellStyle = numberStyle;

                startRow++;
            }

            LapBaoCaoDuLieuTongCong(sheet1, startRow);
            LapBaoCaoDuLieuFooter(sheet1, startRow);
        }

        public void LapBaoCaoDuLieu(Sheet sheet1)
        {
            const int startRow = 9;
            int row = startRow;
            DataFormat format = hssfworkbook.CreateDataFormat();

            Font font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;
            numberStyle.SetFont(font);

            CellStyle sttStyle = hssfworkbook.CreateCellStyle();
            sttStyle.DataFormat = format.GetFormat("#,#0");
            sttStyle.BorderBottom = CellBorderType.THIN;
            sttStyle.BorderLeft = CellBorderType.THIN;
            sttStyle.BorderRight = CellBorderType.THIN;
            sttStyle.BorderTop = CellBorderType.THIN;
            sttStyle.Alignment = HorizontalAlignment.CENTER;
            sttStyle.VerticalAlignment = VerticalAlignment.CENTER;
            sttStyle.SetFont(font);

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;
            textStyle.SetFont(font);

            for (int i = 0; i < dtgvDanhSachHienThi.Rows.Count; i++)
            {
                Row createRow = sheet1.CreateRow(row);
                createRow.Height = 400;

                Cell cell0 = createRow.CreateCell(0);
                cell0.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["STT"].Value.ToString()));
                cell0.CellStyle = sttStyle;

                Cell cell1 = createRow.CreateCell(1);
                cell1.SetCellValue(dtgvDanhSachHienThi.Rows[i].Cells["TENKH"].Value.ToString());
                cell1.CellStyle = textStyle;

                Cell cell2 = createRow.CreateCell(2);
                cell2.SetCellValue(dtgvDanhSachHienThi.Rows[i].Cells["DIACHI"].Value.ToString());
                cell2.CellStyle = textStyle;

                Cell cell3 = createRow.CreateCell(3);
                cell3.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["BPTN"].Value.ToString()));
                cell3.CellStyle = numberStyle;

                Cell cell4 = createRow.CreateCell(4);
                cell4.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["BKTN"].Value.ToString()));
                cell4.CellStyle = numberStyle;

                Cell cell5 = createRow.CreateCell(5);
                cell5.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["EMSTN"].Value.ToString()));
                cell5.CellStyle = numberStyle;

                Cell cell6 = createRow.CreateCell(6);
                cell6.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["EMSNN"].Value.ToString()));
                cell6.CellStyle = numberStyle;

                Cell cell7 = createRow.CreateCell(7);
                cell7.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["VE"].Value.ToString()));
                cell7.CellStyle = numberStyle;

                Cell cell8 = createRow.CreateCell(8);
                cell8.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["BCUT"].Value.ToString()));
                cell8.CellStyle = numberStyle;

                Cell cell9 = createRow.CreateCell(9);
                cell9.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["TBT"].Value.ToString()));
                cell9.CellStyle = numberStyle;

                Cell cell10 = createRow.CreateCell(10);
                cell10.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["TONG"].Value.ToString()));
                cell10.CellStyle = numberStyle;

                Cell cell11 = createRow.CreateCell(11);
                cell11.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["TONGDOANHTHUTRICHTHUONG"].Value.ToString()));
                cell11.CellStyle = numberStyle;

                Cell cell12 = createRow.CreateCell(12);
                cell12.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["TONGDOANHTHUCHIETKHAU"].Value.ToString()));
                cell12.CellStyle = numberStyle;

                Cell cell13 = createRow.CreateCell(13);
                cell13.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["SOTIENTRICHTHUONG"].Value.ToString()));
                cell13.CellStyle = numberStyle;

                Cell cell14 = createRow.CreateCell(14);
                cell14.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["SOTIENCHIETKHAU"].Value.ToString()));
                cell14.CellStyle = numberStyle;

                row++;
            }

            LapBaoCaoDuLieuTongCong(sheet1, row);
            LapBaoCaoDuLieuFooter(sheet1, row);
        }

        public void InitBaoCaoDuLieuHeader(Sheet sheet1)
        {
            //row 7
            Font font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.SetFont(font);
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;

            Row createRow7 = sheet1.CreateRow(6);
            Row createRow8 = sheet1.CreateRow(7);
            Row createRow9 = sheet1.CreateRow(8);

            createRow7.Height = 300;
            createRow8.Height = 800;
            createRow9.Height = 300;

            //row 7-8-9, col 0
            Cell cell70 = createRow7.CreateCell(0);
            Cell cell80 = createRow8.CreateCell(0);
            Cell cell90 = createRow9.CreateCell(0);

            cell70.SetCellValue("STT");
            cell70.CellStyle = textStyle;
            cell80.CellStyle = textStyle;
            cell90.CellStyle = textStyle;

            CellRangeAddress region = new CellRangeAddress(6, 8, 0, 0);
            sheet1.AddMergedRegion(region);

            //Row 7-8-9, col 1
            Cell cell71 = createRow7.CreateCell(1);
            Cell cell81 = createRow8.CreateCell(1);
            Cell cell91 = createRow9.CreateCell(1);

            cell71.SetCellValue("Tên khách hàng");
            cell71.CellStyle = textStyle;
            cell81.CellStyle = textStyle;
            cell91.CellStyle = textStyle;

            region = new CellRangeAddress(6, 8, 1, 1);
            sheet1.AddMergedRegion(region);

            //Row 7-8-9, col 2
            Cell cell72 = createRow7.CreateCell(2);
            Cell cell82 = createRow8.CreateCell(2);
            Cell cell92 = createRow9.CreateCell(2);

            cell72.SetCellValue("Địa chỉ khách hàng");
            cell72.CellStyle = textStyle;
            cell82.CellStyle = textStyle;
            cell92.CellStyle = textStyle;

            region = new CellRangeAddress(6, 8, 2, 2);
            sheet1.AddMergedRegion(region);

            //Row 7, col d,e,f,g,h,i,j,k,l,m
            Cell cell73 = createRow7.CreateCell(3);
            Cell cell74 = createRow7.CreateCell(4);
            Cell cell75 = createRow7.CreateCell(5);
            Cell cell76 = createRow7.CreateCell(6);
            Cell cell77 = createRow7.CreateCell(7);
            Cell cell78 = createRow7.CreateCell(8);
            Cell cell79 = createRow7.CreateCell(9);
            Cell cell710 = createRow7.CreateCell(10);
            Cell cell711 = createRow7.CreateCell(11);
            Cell cell712 = createRow7.CreateCell(12);
            Cell cell713 = createRow7.CreateCell(13);
            Cell cell714 = createRow7.CreateCell(14);

            cell73.SetCellValue("Doanh thu các dịch vụ Bưu Chính");
            cell73.CellStyle = textStyle;
            cell74.CellStyle = textStyle;
            cell75.CellStyle = textStyle;
            cell76.CellStyle = textStyle;
            cell77.CellStyle = textStyle;
            cell78.CellStyle = textStyle;
            cell79.CellStyle = textStyle;
            cell710.CellStyle = textStyle;
            cell711.CellStyle = textStyle;
            cell712.CellStyle = textStyle;
            cell713.CellStyle = textStyle;
            cell714.CellStyle = textStyle;

            region = new CellRangeAddress(6, 6, 3, 14);
            sheet1.AddMergedRegion(region);

            //Row 8, col d
            Cell cell83 = createRow8.CreateCell(3);
            Cell cell93 = createRow9.CreateCell(3);
            cell83.CellStyle = textStyle;
            cell93.CellStyle = textStyle;

            Cell cell84 = createRow8.CreateCell(4);
            Cell cell94 = createRow9.CreateCell(4);
            cell84.CellStyle = textStyle;
            cell94.CellStyle = textStyle;

            Cell cell85 = createRow8.CreateCell(5);
            Cell cell95 = createRow9.CreateCell(5);
            cell85.CellStyle = textStyle;
            cell95.CellStyle = textStyle;

            Cell cell86 = createRow8.CreateCell(6);
            Cell cell96 = createRow9.CreateCell(6);
            cell86.CellStyle = textStyle;
            cell96.CellStyle = textStyle;

            Cell cell87 = createRow8.CreateCell(7);
            Cell cell97 = createRow9.CreateCell(7);
            cell87.CellStyle = textStyle;
            cell97.CellStyle = textStyle;

            Cell cell88 = createRow8.CreateCell(8);
            Cell cell98 = createRow9.CreateCell(8);
            cell88.CellStyle = textStyle;
            cell98.CellStyle = textStyle;

            Cell cell89 = createRow8.CreateCell(9);
            Cell cell99 = createRow9.CreateCell(9);
            cell89.CellStyle = textStyle;
            cell99.CellStyle = textStyle;

            Cell cell810 = createRow8.CreateCell(10);
            Cell cell910 = createRow9.CreateCell(10);
            cell810.CellStyle = textStyle;
            cell910.CellStyle = textStyle;

            Cell cell811 = createRow8.CreateCell(11);
            Cell cell911 = createRow9.CreateCell(11);
            CellStyle textStyle1 = hssfworkbook.CreateCellStyle();
            textStyle1.SetFont(font);
            textStyle1.Alignment = HorizontalAlignment.CENTER;
            textStyle1.VerticalAlignment = VerticalAlignment.JUSTIFY;
            textStyle1.BorderBottom = CellBorderType.THIN;
            textStyle1.BorderLeft = CellBorderType.THIN;
            textStyle1.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;

            Cell cell812 = createRow8.CreateCell(12);
            Cell cell912 = createRow9.CreateCell(12);
            cell812.CellStyle = textStyle;
            cell912.CellStyle = textStyle;

            Cell cell813 = createRow8.CreateCell(13);
            Cell cell913 = createRow9.CreateCell(13);
            cell813.CellStyle = textStyle;
            cell913.CellStyle = textStyle;

            Cell cell814 = createRow8.CreateCell(14);
            Cell cell914 = createRow9.CreateCell(14);
            cell814.CellStyle = textStyle;
            cell914.CellStyle = textStyle;

            cell811.CellStyle = textStyle1;
            cell911.CellStyle = textStyle1;
            cell812.CellStyle = textStyle1;
            cell912.CellStyle = textStyle1;
            cell813.CellStyle = textStyle1;
            cell913.CellStyle = textStyle1;
            cell814.CellStyle = textStyle1;
            cell914.CellStyle = textStyle1;

            cell83.SetCellValue("Bưu phẩm");
            cell84.SetCellValue("Bưu kiện");
            cell85.SetCellValue("EMS");
            cell87.SetCellValue("VEXPRESS");
            cell88.SetCellValue("BCUT");
            cell89.SetCellValue("THƯ/BƯU THIẾP");
            cell810.SetCellValue("Tổng");
            cell811.SetCellValue("Tổng doanh thu trích thưởng");
            cell812.SetCellValue("Tổng doanh thu chiết khấu");
            cell813.SetCellValue("Số tiền chi trích thưởng");
            cell814.SetCellValue("Số tiền chi chiết khấu");

            cell93.SetCellValue("TN");
            cell94.SetCellValue("TN");
            cell95.SetCellValue("TN");
            cell96.SetCellValue("TN");

            region = new CellRangeAddress(7, 7, 5, 6);
            sheet1.AddMergedRegion(region);

            region = new CellRangeAddress(7, 8, 7, 7);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(7, 8, 8, 8);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(7, 8, 9, 9);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(7, 8, 10, 10);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(7, 8, 11, 11);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(7, 8, 12, 12);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(7, 8, 13, 13);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(7, 8, 14, 14);
            sheet1.AddMergedRegion(region);
        }

        public void InitBaoCaoDuLieuNgayThang(Sheet sheet1)
        {
            //row 1
            Font font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            Row createRow = sheet1.CreateRow(0);
            createRow.Height = 400;

            Cell cell = createRow.CreateCell(0);
            cell.SetCellValue("BƯU ĐIỆN BÌNH DƯƠNG");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);

            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
            sheet1.AddMergedRegion(region);

            //row 2
            font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 12;
            font.FontName = "Times New Roman";

            textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            createRow = sheet1.CreateRow(1);
            createRow.Height = 400;

            cell = createRow.CreateCell(0);
            cell.SetCellValue("TRUNG TÂM GIAO DỊCH");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);

            region = new CellRangeAddress(1, 1, 0, 2);
            sheet1.AddMergedRegion(region);

            //row 4
            font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 15;
            font.FontName = "Times New Roman";

            textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            createRow = sheet1.CreateRow(3);
            createRow.Height = 400;

            cell = createRow.CreateCell(0);
            cell.SetCellValue("BÁO CÁO KHÁCH HÀNG LỚN DỊCH VỤ BƯU CHÍNH");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);
            cell = createRow.CreateCell(3);
            cell = createRow.CreateCell(4);
            cell = createRow.CreateCell(5);
            cell = createRow.CreateCell(6);
            cell = createRow.CreateCell(7);
            cell = createRow.CreateCell(8);
            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);
            cell = createRow.CreateCell(11);
            cell = createRow.CreateCell(12);

            region = new CellRangeAddress(3, 3, 0, 12);
            sheet1.AddMergedRegion(region);

            //row 5
            font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            createRow = sheet1.CreateRow(4);
            createRow.Height = 400;

            cell = createRow.CreateCell(0);
            cell.SetCellValue("Tháng " + thang.ToString() + " năm " + nam.ToString());
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);
            cell = createRow.CreateCell(3);
            cell = createRow.CreateCell(4);
            cell = createRow.CreateCell(5);
            cell = createRow.CreateCell(6);
            cell = createRow.CreateCell(7);
            cell = createRow.CreateCell(8);
            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);
            cell = createRow.CreateCell(11);
            cell = createRow.CreateCell(12);

            region = new CellRangeAddress(4, 4, 0, 12);
            sheet1.AddMergedRegion(region);

            //row 6
            font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            createRow = sheet1.CreateRow(5);
            createRow.Height = 400;

            cell = createRow.CreateCell(0);
            cell.SetCellValue("( Mẫu này báo cáo theo từng tháng về phòng KD trước ngày 5 tây hàng tháng )");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);
            cell = createRow.CreateCell(3);
            cell = createRow.CreateCell(4);
            cell = createRow.CreateCell(5);
            cell = createRow.CreateCell(6);
            cell = createRow.CreateCell(7);
            cell = createRow.CreateCell(8);
            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);
            cell = createRow.CreateCell(11);
            cell = createRow.CreateCell(12);

            region = new CellRangeAddress(5, 5, 0, 12);
            sheet1.AddMergedRegion(region);
        }

        public void InitBaoCaoTongHopNgayThang(Sheet sheet1)
        {
            //row 1
            Font font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            Row createRow = sheet1.CreateRow(0);
            createRow.Height = 400;

            Cell cell = createRow.CreateCell(0);
            cell.SetCellValue("TỔNG CÔNG TY BƯU CHÍNH VIỆT NAM ");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);

            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
            sheet1.AddMergedRegion(region);

            //row 2
            font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            createRow = sheet1.CreateRow(1);
            createRow.Height = 400;

            cell = createRow.CreateCell(0);
            cell.SetCellValue("BƯU ĐIỆN BÌNH DƯƠNG");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);

            region = new CellRangeAddress(1, 1, 0, 2);
            sheet1.AddMergedRegion(region);

            //row 4
            font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 16;
            font.FontName = "Times New Roman";

            textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            createRow = sheet1.CreateRow(3);
            createRow.Height = 400;

            cell = createRow.CreateCell(0);
            cell.SetCellValue("DANH SÁCH KHÁCH HÀNG LỚN SỬ DỤNG DỊCH VỤ BƯU CHÍNH");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);
            cell = createRow.CreateCell(3);
            cell = createRow.CreateCell(4);
            cell = createRow.CreateCell(5);
            cell = createRow.CreateCell(6);
            cell = createRow.CreateCell(7);
            cell = createRow.CreateCell(8);
            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);
            cell = createRow.CreateCell(11);
            cell = createRow.CreateCell(12);
            cell = createRow.CreateCell(13);
            cell = createRow.CreateCell(14);

            region = new CellRangeAddress(3, 3, 0, 12);
            sheet1.AddMergedRegion(region);

            //row 5
            font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 11;
            font.FontName = "Times New Roman";

            textStyle = hssfworkbook.CreateCellStyle();
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            createRow = sheet1.CreateRow(5);
            createRow.Height = 400;

            cell = createRow.CreateCell(0);
            cell.SetCellValue("KHU VỰC " + Utils.GetTenDonVi(objDonViDS.Tables[0], maDonVi));
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell = createRow.CreateCell(2);
            cell = createRow.CreateCell(3);
            cell = createRow.CreateCell(4);
            cell = createRow.CreateCell(5);
            cell = createRow.CreateCell(6);
            cell = createRow.CreateCell(7);
            cell = createRow.CreateCell(8);
            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);
            cell = createRow.CreateCell(11);
            cell = createRow.CreateCell(12);
            cell = createRow.CreateCell(13);
            cell = createRow.CreateCell(14);

            region = new CellRangeAddress(3, 3, 0, 12);
            sheet1.AddMergedRegion(region);

            region = new CellRangeAddress(5, 5, 0, 12);
            sheet1.AddMergedRegion(region);
        }

        public void InitBaoCaoTongHopHeader(Sheet sheet1)
        {
            Font font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.SetFont(font);
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;

            Row createRow = sheet1.CreateRow(6);
            createRow.Height = 400;

            Cell cell = createRow.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(1);
            cell.SetCellValue("TÊN KHÁCH HÀNG");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(2);
            cell.SetCellValue("ĐỊA CHỈ");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(3);
            cell.SetCellValue("T.1");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(4);
            cell.SetCellValue("T.2");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(5);
            cell.SetCellValue("T.3");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(6);
            cell.SetCellValue("T.4");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(7);
            cell.SetCellValue("T.5");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(8);
            cell.SetCellValue("T.6");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(9);
            cell.SetCellValue("T.7");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(10);
            cell.SetCellValue("T.8");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(11);
            cell.SetCellValue("T.9");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(12);
            cell.SetCellValue("T.10");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(13);
            cell.SetCellValue("T.11");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(14);
            cell.SetCellValue("T.12");
            cell.CellStyle = textStyle;
        }

        public void LapBaoCaoDuLieuTongCong(Sheet sheet1, int row)
        {
            DataFormat format = hssfworkbook.CreateDataFormat();
            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            Font font1 = hssfworkbook.CreateFont();
            font1.Color = HSSFColor.RED.index;
            font1.Boldweight = (short)FontBoldWeight.BOLD;
            font1.FontHeightInPoints = 10;
            font1.FontName = "Times New Roman";

            Font font2 = hssfworkbook.CreateFont();
            font2.Boldweight = (short)FontBoldWeight.BOLD;
            font2.FontHeightInPoints = 10;
            font2.FontName = "Times New Roman";

            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.SetFont(font1);
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font2);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            Row createRow = sheet1.CreateRow(row);
            createRow.Height = 400;

            Cell cell0 = createRow.CreateCell(0);
            cell0.SetCellValue("Tổng cộng");
            cell0.CellStyle = textStyle;

            Cell cell1 = createRow.CreateCell(1);
            cell1.CellStyle = textStyle;

            Cell cell2 = createRow.CreateCell(2);
            cell2.SetCellValue(" ");
            cell2.CellStyle = textStyle;
            
            string formula = "SUM(D10:D" + row.ToString() + ")";
            Cell cell3 = createRow.CreateCell(3);
            cell3.CellFormula = formula;
            cell3.CellStyle = numberStyle;

            formula = "SUM(E10:E" + row.ToString() + ")";
            Cell cell4 = createRow.CreateCell(4);
            cell4.CellFormula = formula;
            cell4.CellStyle = numberStyle;

            formula = "SUM(F10:F" + row.ToString() + ")";
            Cell cell5 = createRow.CreateCell(5);
            cell5.CellFormula = formula;
            cell5.CellStyle = numberStyle;

            formula = "SUM(G10:G" + row.ToString() + ")";
            Cell cell6 = createRow.CreateCell(6);
            cell6.CellFormula = formula;
            cell6.CellStyle = numberStyle;

            formula = "SUM(H10:H" + row.ToString() + ")";
            Cell cell7 = createRow.CreateCell(7);
            cell7.CellFormula = formula;
            cell7.CellStyle = numberStyle;

            formula = "SUM(I10:I" + row.ToString() + ")";
            Cell cell8 = createRow.CreateCell(8);
            cell8.CellFormula = formula;
            cell8.CellStyle = numberStyle;

            formula = "SUM(J10:J" + row.ToString() + ")";
            Cell cell9 = createRow.CreateCell(9);
            cell9.CellFormula = formula;
            cell9.CellStyle = numberStyle;

            formula = "SUM(K10:K" + row.ToString() + ")";
            Cell cell10 = createRow.CreateCell(10);
            cell10.CellFormula = formula;
            cell10.CellStyle = numberStyle;

            formula = "SUM(L10:L" + row.ToString() + ")";
            Cell cell11 = createRow.CreateCell(11);
            cell11.CellFormula = formula;
            cell11.CellStyle = numberStyle;

            formula = "SUM(M10:M" + row.ToString() + ")";
            //Don't know why col 12 is unhide
            Cell cell12 = createRow.CreateCell(12);
            cell12.CellFormula = formula;
            cell12.CellStyle = numberStyle;

            formula = "SUM(N10:N" + row.ToString() + ")";
            //Don't know why col 12 is unhide
            Cell cell13 = createRow.CreateCell(13);
            cell13.CellFormula = formula;
            cell13.CellStyle = numberStyle;

            formula = "SUM(O10:O" + row.ToString() + ")";
            //Don't know why col 12 is unhide
            Cell cell14 = createRow.CreateCell(14);
            cell14.CellFormula = formula;
            cell14.CellStyle = numberStyle;

            CellRangeAddress region = new CellRangeAddress(row, row, 0, 2);
            sheet1.AddMergedRegion(region);
        }

        public void LapBaoCaoDuLieuFooter(Sheet sheet1, int row)
        {
            DataFormat format = hssfworkbook.CreateDataFormat();
            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            Font font1 = hssfworkbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.BOLD;
            font1.FontHeightInPoints = 10;
            font1.FontName = "Times New Roman";

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.SetFont(font1);
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.VerticalAlignment = VerticalAlignment.TOP;

            Row createRow = sheet1.CreateRow(row + 2);
            Cell cell = createRow.CreateCell(1);
            cell.SetCellValue("Người lập biểu");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(8);
            cell.SetCellValue("Trưởng đơn vị");
            cell.CellStyle = textStyle;
            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);

            CellRangeAddress region = new CellRangeAddress(row + 2, row + 7, 8, 10);
            sheet1.AddMergedRegion(region);

            Row createRow2 = sheet1.CreateRow(row + 7);
            cell = createRow2.CreateCell(1);
            cell.SetCellValue("( ký tên ghi họ tên )");
            cell.CellStyle = textStyle;
        }

        public void LapBaoCaoTongHopFooter(Sheet sheet1, int row)
        {
            DataFormat format = hssfworkbook.CreateDataFormat();
            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            Font font1 = hssfworkbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.BOLD;
            font1.FontHeightInPoints = 10;
            font1.FontName = "Times New Roman";

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.SetFont(font1);
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            Row createRow = sheet1.CreateRow(row + 2);
            createRow.Height = 400;
            Cell cell = createRow.CreateCell(8);
            var objBUS = new DonViBUS(GlobalVariables.g_strConnectionString);
            cell.SetCellValue(Utils.GetTenDonVi(objDonViDS.Tables[0], maDonVi) + ", ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm 2010");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);
            cell = createRow.CreateCell(11);
            cell = createRow.CreateCell(12);
            cell = createRow.CreateCell(13);

            createRow = sheet1.CreateRow(row + 3);
            createRow.Height = 400;
            cell = createRow.CreateCell(8);
            cell.SetCellValue("GIÁM ĐỐC");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(9);
            cell = createRow.CreateCell(10);
            cell = createRow.CreateCell(11);
            cell = createRow.CreateCell(12);
            cell = createRow.CreateCell(13);

            CellRangeAddress region = new CellRangeAddress(row + 2, row + 2, 8, 13);
            sheet1.AddMergedRegion(region);
            region = new CellRangeAddress(row + 3, row + 3, 8, 13);
            sheet1.AddMergedRegion(region);
        }

        public void LapBaoCaoTongHopTongCong(Sheet sheet1, int row)
        {
            DataFormat format = hssfworkbook.CreateDataFormat();
            Font font1 = hssfworkbook.CreateFont();
            font1.Color = HSSFColor.RED.index;
            font1.Boldweight = (short)FontBoldWeight.BOLD;
            font1.FontHeightInPoints = 10;
            font1.FontName = "Times New Roman";

            Font font2 = hssfworkbook.CreateFont();
            font2.Boldweight = (short)FontBoldWeight.BOLD;
            font2.FontHeightInPoints = 10;
            font2.FontName = "Times New Roman";

            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            numberStyle.SetFont(font1);
            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font2);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            Row createRow = sheet1.CreateRow(row);
            createRow.Height = 400;

            Cell cell0 = createRow.CreateCell(0);
            cell0.SetCellValue("Tổng cộng");
            cell0.CellStyle = textStyle;

            Cell cell1 = createRow.CreateCell(1);
            cell1.CellStyle = textStyle;

            Cell cell2 = createRow.CreateCell(2);
            cell2.SetCellValue(" ");
            cell2.CellStyle = textStyle;

            string formula = "SUM(D8:D" + row.ToString() + ")";
            Cell cell3 = createRow.CreateCell(3);
            cell3.CellFormula = formula;
            cell3.CellStyle = numberStyle;

            formula = "SUM(E8:E" + row.ToString() + ")";
            Cell cell4 = createRow.CreateCell(4);
            cell4.CellFormula = formula;
            cell4.CellStyle = numberStyle;

            formula = "SUM(F8:F" + row.ToString() + ")";
            Cell cell5 = createRow.CreateCell(5);
            cell5.CellFormula = formula;
            cell5.CellStyle = numberStyle;

            formula = "SUM(G8:G" + row.ToString() + ")";
            Cell cell6 = createRow.CreateCell(6);
            cell6.CellFormula = formula;
            cell6.CellStyle = numberStyle;

            formula = "SUM(H8:H" + row.ToString() + ")";
            Cell cell7 = createRow.CreateCell(7);
            cell7.CellFormula = formula;
            cell7.CellStyle = numberStyle;

            formula = "SUM(I8:I" + row.ToString() + ")";
            Cell cell8 = createRow.CreateCell(8);
            cell8.CellFormula = formula;
            cell8.CellStyle = numberStyle;

            formula = "SUM(J8:J" + row.ToString() + ")";
            Cell cell9 = createRow.CreateCell(9);
            cell9.CellFormula = formula;
            cell9.CellStyle = numberStyle;

            formula = "SUM(K8:K" + row.ToString() + ")";
            Cell cell10 = createRow.CreateCell(10);
            cell10.CellFormula = formula;
            cell10.CellStyle = numberStyle;

            formula = "SUM(L8:L" + row.ToString() + ")";
            Cell cell11 = createRow.CreateCell(11);
            cell11.CellFormula = formula;
            cell11.CellStyle = numberStyle;

            formula = "SUM(M8:M" + row.ToString() + ")";
            //Don't know why col 12 is unhide
            Cell cell12 = createRow.CreateCell(12);
            cell12.CellFormula = formula;
            cell12.CellStyle = numberStyle;

            formula = "SUM(N8:N" + row.ToString() + ")";
            //Don't know why col 12 is unhide
            Cell cell13 = createRow.CreateCell(13);
            cell13.CellFormula = formula;
            cell13.CellStyle = numberStyle;

            formula = "SUM(O8:O" + row.ToString() + ")";
            //Don't know why col 12 is unhide
            Cell cell14 = createRow.CreateCell(14);
            cell14.CellFormula = formula;
            cell14.CellStyle = numberStyle;

            CellRangeAddress region = new CellRangeAddress(row, row, 0, 2);
            sheet1.AddMergedRegion(region);
        }

        public void LapBaoCaoTongHop(Sheet sheet1)
        {
            const int startRow = 7;
            int row = startRow;
            DataFormat format = hssfworkbook.CreateDataFormat();
            Font font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;
            numberStyle.SetFont(font);

            CellStyle sttStyle = hssfworkbook.CreateCellStyle();
            sttStyle.DataFormat = format.GetFormat("#,#0");
            sttStyle.BorderBottom = CellBorderType.THIN;
            sttStyle.BorderLeft = CellBorderType.THIN;
            sttStyle.BorderRight = CellBorderType.THIN;
            sttStyle.BorderTop = CellBorderType.THIN;
            sttStyle.Alignment = HorizontalAlignment.CENTER;
            sttStyle.VerticalAlignment = VerticalAlignment.CENTER;
            sttStyle.SetFont(font);

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;
            textStyle.SetFont(font);

            var objBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
            var objTHByNamTable = objBUS.SelectByNAM(nam).Tables[0];
            var objKHBUS = new KhachHangBUS(GlobalVariables.g_strConnectionString);
            var objKHDS = objKHBUS.SelectByMADONVI(maDonVi);

            for (int i = 0; i < objKHDS.Tables[0].Rows.Count; i++)
            {
                var rowKH = objKHDS.Tables[0].Rows[i];
                Row createRow = sheet1.CreateRow(row);
                createRow.Height = 400;

                Cell cell0 = createRow.CreateCell(0);
                cell0.SetCellValue(i + 1);
                cell0.CellStyle = sttStyle;

                Cell cell1 = createRow.CreateCell(1);
                cell1.SetCellValue(rowKH["TENKH"].ToString());
                cell1.CellStyle = textStyle;

                Cell cell2 = createRow.CreateCell(2);
                cell2.SetCellValue(rowKH["DIACHI"].ToString());
                cell2.CellStyle = textStyle;

                bool contain = false;
                for (int j = 1; j <= 12; j++)
                {
                    contain = false;
                    for (int k = 0; k < objTHByNamTable.Rows.Count; k++)
                    {
                        if (int.Parse(rowKH["MAKH"].ToString()) == int.Parse(objTHByNamTable.Rows[k]["MAKH"].ToString()))
                        {
                            if (int.Parse(objTHByNamTable.Rows[k]["THANG"].ToString()) == j)
                            {
                                cell2 = createRow.CreateCell(j + 2);
                                cell2.SetCellValue(long.Parse(objTHByNamTable.Rows[k]["TONGTIEN"].ToString()));
                                cell2.CellStyle = numberStyle;
                                contain = true;
                                break;
                            }
                        }
                    }
                    if (!contain)
                    {
                        cell2 = createRow.CreateCell(j + 2);
                        cell2.SetCellValue(0);
                        cell2.CellStyle = numberStyle;
                    }
                }

                row++;
            }

            LapBaoCaoTongHopTongCong(sheet1, row);
            LapBaoCaoTongHopFooter(sheet1, row);
        }

        public void LapBaoCaoTongHop_CacDonViConLai(Sheet sheet1)
        {
            const int startRow = 7;
            int row = startRow;
            DataFormat format = hssfworkbook.CreateDataFormat();
            Font font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "Times New Roman";

            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;
            numberStyle.SetFont(font);

            CellStyle sttStyle = hssfworkbook.CreateCellStyle();
            sttStyle.DataFormat = format.GetFormat("#,#0");
            sttStyle.BorderBottom = CellBorderType.THIN;
            sttStyle.BorderLeft = CellBorderType.THIN;
            sttStyle.BorderRight = CellBorderType.THIN;
            sttStyle.BorderTop = CellBorderType.THIN;
            sttStyle.Alignment = HorizontalAlignment.CENTER;
            sttStyle.VerticalAlignment = VerticalAlignment.CENTER;
            sttStyle.SetFont(font);

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;
            textStyle.SetFont(font);

            var objBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
            var objTHByNamTable = objBUS.SelectByNAM(nam).Tables[0];
            var objKHBUS = new KhachHangBUS(GlobalVariables.g_strConnectionString);
            var objKHDS = objKHBUS.SelectByMADONVI(maDonVi);

            for (int i = 0; i < objKHDS.Tables[0].Rows.Count; i++)
            {
                var rowKH = objKHDS.Tables[0].Rows[i];
                Row createRow = sheet1.CreateRow(row);
                createRow.Height = 400;

                Cell cell0 = createRow.CreateCell(0);
                cell0.SetCellValue(i+1);
                cell0.CellStyle = sttStyle;

                Cell cell1 = createRow.CreateCell(1);
                cell1.SetCellValue(rowKH["TENKH"].ToString());
                cell1.CellStyle = textStyle;

                Cell cell2 = createRow.CreateCell(2);
                cell2.SetCellValue(rowKH["DIACHI"].ToString());
                cell2.CellStyle = textStyle;

                bool contain = false;
                for (int j = 1; j <= 12; j++)
                {
                    contain = false;
                    for (int k = 0; k < objTHByNamTable.Rows.Count; k++)
                    {
                        if (int.Parse(rowKH["MAKH"].ToString()) == int.Parse(objTHByNamTable.Rows[k]["MAKH"].ToString()))
                        {
                            if (int.Parse(objTHByNamTable.Rows[k]["THANG"].ToString()) == j)
                            {
                                cell2 = createRow.CreateCell(j + 2);
                                cell2.SetCellValue(long.Parse(objTHByNamTable.Rows[k]["TONGTIEN"].ToString()));
                                cell2.CellStyle = numberStyle;
                                contain = true;
                                break;
                            }
                        }
                    }
                    if (!contain)
                    {
                        cell2 = createRow.CreateCell(j + 2);
                        cell2.SetCellValue(0);
                        cell2.CellStyle = numberStyle;
                    }
                }

                row++;
            }

            LapBaoCaoTongHopTongCong(sheet1, row);
            LapBaoCaoTongHopFooter(sheet1, row);
        }

        public bool CheckingDonViHaveAnyKhachHang(DataTable tbl, string madonvi, int thang, int nam)
        {
            for (int i = 0; i < tbl.Rows.Count; i++)
            {
                if (tbl.Rows[i]["MADONVI"].ToString() == madonvi &&
                    int.Parse(tbl.Rows[i]["THANG"].ToString()) == thang &&
                    int.Parse(tbl.Rows[i]["NAM"].ToString()) == nam)
                    return true;
            }
            return false;
        }

        public void LapBaoCaoCacDonViConLai()
        {
            var maDonViCompare = maDonVi;
            var objBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
            var objDS = objBUS.CheckingDonViHaveAnyKhachHang();

            for (int i = 0; i < objDonViDS.Tables[0].Rows.Count; i++)
            {
                if (maDonViCompare != objDonViDS.Tables[0].Rows[i]["MADONVI"].ToString())
                {
                    maDonVi = objDonViDS.Tables[0].Rows[i]["MADONVI"].ToString();
                    if (CheckingDonViHaveAnyKhachHang(objDS.Tables[0], maDonVi, thang, nam))
                    {
                        Sheet sheet1 = hssfworkbook.CreateSheet(maDonVi);
                        if (sheet1 != null)
                        {
                            InitSheet(sheet1);
                            InitBaoCaoDuLieuNgayThang(sheet1);
                            InitBaoCaoDuLieuHeader(sheet1);
                            LapBaoCaoDuLieu_CacDonViConLai(sheet1);

                            Sheet sheet2 = hssfworkbook.CreateSheet("TONG HOP-" + maDonVi);
                            InitSheetTongHop(sheet2);
                            InitBaoCaoTongHopNgayThang(sheet2);
                            InitBaoCaoTongHopHeader(sheet2);
                            LapBaoCaoTongHop_CacDonViConLai(sheet2);
                        }
                    }
                }
            }
        }

        public void InitSheet(Sheet sheet1)
        {
            sheet1.SetColumnWidth(0, 4 * 250);
            sheet1.SetColumnWidth(1, 25 * 250);
            sheet1.SetColumnWidth(2, 27 * 250);
            sheet1.SetColumnWidth(3, 10 * 250);
            sheet1.SetColumnWidth(4, 10 * 250);
            sheet1.SetColumnWidth(5, 10 * 250);
            sheet1.SetColumnWidth(6, 10 * 250);
            sheet1.SetColumnWidth(7, 10 * 250);
            sheet1.SetColumnWidth(8, 10 * 250);
            sheet1.SetColumnWidth(9, 15 * 250);
            sheet1.SetColumnWidth(10, 10 * 250);
            sheet1.SetColumnWidth(11, 10 * 250);
            sheet1.SetColumnWidth(12, 10 * 250);
            sheet1.SetColumnWidth(13, 10 * 250);
        }

        public void InitSheetTongHop(Sheet sheet1)
        {
            sheet1.SetColumnWidth(0, 4 * 250);
            sheet1.SetColumnWidth(1, 31 * 250);
            sheet1.SetColumnWidth(2, 37 * 250);
            sheet1.SetColumnWidth(3, 10 * 250);
            sheet1.SetColumnWidth(4, 10 * 250);
            sheet1.SetColumnWidth(5, 10 * 250);
            sheet1.SetColumnWidth(6, 10 * 250);
            sheet1.SetColumnWidth(7, 10 * 250);
            sheet1.SetColumnWidth(8, 10 * 250);
            sheet1.SetColumnWidth(9, 10 * 250);
            sheet1.SetColumnWidth(10, 10 * 250);
            sheet1.SetColumnWidth(11, 10 * 250);
            sheet1.SetColumnWidth(12, 10 * 250);
        }

        private void btnLapBaoCao_Click(object sender, EventArgs e)
        {
            if (dtgvDanhSachHienThi.Rows.Count > 0)
            {
                var ASynInvoke = new TimeTask(WaitLoad);
                IAsyncResult result = ASynInvoke.BeginInvoke(null, null);

                InitializeWorkbook();
                //Create sheet donvi
                Sheet sheet1 = hssfworkbook.CreateSheet(maDonVi);
                if (sheet1 != null)
                {
                    InitSheet(sheet1);
                    InitBaoCaoDuLieuNgayThang(sheet1);
                    InitBaoCaoDuLieuHeader(sheet1);
                    LapBaoCaoDuLieu(sheet1);


                    Sheet sheet2 = hssfworkbook.CreateSheet("TONG HOP-" + maDonVi);
                    InitSheetTongHop(sheet2);
                    InitBaoCaoTongHopNgayThang(sheet2);
                    InitBaoCaoTongHopHeader(sheet2);
                    LapBaoCaoTongHop(sheet2);

                    LapBaoCaoCacDonViConLai();

                    WriteToFile();
                    System.Diagnostics.Process.Start(Application.StartupPath + @"/Exports/BaoCao_KhachHangLon_" + thang.ToString() + "_" + nam.ToString() + ".xls");

                    waiting.Close();
                    waiting.Dispose();
                    waiting = null;

                    ASynInvoke.EndInvoke(result);
                }
            }
            else
            {
                MessageBox.Show("Không có dữ liệu để xuất báo cáo", "Bao Loi",
                       MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void WriteToFile()
        {
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(Application.StartupPath + @"/Exports/BaoCao_KhachHangLon_" + thang.ToString() + "_" + nam.ToString() +".xls", FileMode.Create);
            try
            {
                waiting.Hide();
                hssfworkbook.Write(file);
                MessageBox.Show("Lập báo cáo thành công", "Thong bao",
                       MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch
            {
                waiting.Hide();
                MessageBox.Show("Lập báo cáo thất bại", "Bao Loi",
                       MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            file.Close();
        }

        public void InitializeWorkbook()
        {
            hssfworkbook = new HSSFWorkbook();

            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }

        private void pnlTimKiemCauThuScreen_Paint(object sender, PaintEventArgs e)
        {

        }
    }
}
